package com.rickpan.repository;

import com.rickpan.entity.TeamInviteCode;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 团队邀请码数据访问层
 */
@Repository
public interface TeamInviteCodeRepository extends JpaRepository<TeamInviteCode, Long> {

    /**
     * 根据邀请码查找
     */
    Optional<TeamInviteCode> findByInviteCode(String inviteCode);

    /**
     * 根据团队ID查找所有邀请码
     */
    List<TeamInviteCode> findByTeamId(Long teamId);

    /**
     * 根据团队ID和状态查找邀请码
     */
    List<TeamInviteCode> findByTeamIdAndStatus(Long teamId, TeamInviteCode.Status status);

    /**
     * 根据创建者ID查找邀请码
     */
    List<TeamInviteCode> findByCreatorId(Long creatorId);

    /**
     * 分页查询团队的邀请码
     */
    Page<TeamInviteCode> findByTeamId(Long teamId, Pageable pageable);

    /**
     * 查找活跃的邀请码
     */
    @Query("SELECT tic FROM TeamInviteCode tic WHERE tic.teamId = :teamId AND tic.status = 'ACTIVE'")
    List<TeamInviteCode> findActiveByTeamId(@Param("teamId") Long teamId);

    /**
     * 查找有效的邀请码（活跃且未过期且未用完）
     */
    @Query("SELECT tic FROM TeamInviteCode tic WHERE tic.inviteCode = :inviteCode " +
           "AND tic.status = 'ACTIVE' " +
           "AND (tic.expireTime IS NULL OR tic.expireTime > :now) " +
           "AND (tic.maxUses IS NULL OR tic.usedCount < tic.maxUses)")
    Optional<TeamInviteCode> findValidByInviteCode(@Param("inviteCode") String inviteCode, 
                                                   @Param("now") LocalDateTime now);

    /**
     * 查找过期的邀请码
     */
    @Query("SELECT tic FROM TeamInviteCode tic WHERE tic.status = 'ACTIVE' " +
           "AND tic.expireTime IS NOT NULL AND tic.expireTime <= :now")
    List<TeamInviteCode> findExpiredCodes(@Param("now") LocalDateTime now);

    /**
     * 查找用完的邀请码
     */
    @Query("SELECT tic FROM TeamInviteCode tic WHERE tic.status = 'ACTIVE' " +
           "AND tic.maxUses IS NOT NULL AND tic.usedCount >= tic.maxUses")
    List<TeamInviteCode> findExhaustedCodes();

    /**
     * 统计团队的活跃邀请码数量
     */
    @Query("SELECT COUNT(tic) FROM TeamInviteCode tic WHERE tic.teamId = :teamId AND tic.status = 'ACTIVE'")
    long countActiveByTeamId(@Param("teamId") Long teamId);

    /**
     * 统计用户创建的邀请码数量
     */
    long countByCreatorId(Long creatorId);

    /**
     * 检查邀请码是否存在
     */
    boolean existsByInviteCode(String inviteCode);

    /**
     * 删除团队的所有邀请码
     */
    void deleteByTeamId(Long teamId);

    /**
     * 根据团队ID和创建者ID查找邀请码
     */
    List<TeamInviteCode> findByTeamIdAndCreatorId(Long teamId, Long creatorId);

    /**
     * 查找即将过期的邀请码（24小时内过期）
     */
    @Query("SELECT tic FROM TeamInviteCode tic WHERE tic.status = 'ACTIVE' " +
           "AND tic.expireTime IS NOT NULL " +
           "AND tic.expireTime > :now " +
           "AND tic.expireTime <= :expireSoon")
    List<TeamInviteCode> findExpiringSoon(@Param("now") LocalDateTime now, 
                                          @Param("expireSoon") LocalDateTime expireSoon);

    /**
     * 查找最近创建的邀请码
     */
    @Query("SELECT tic FROM TeamInviteCode tic WHERE tic.teamId = :teamId " +
           "ORDER BY tic.createdAt DESC")
    List<TeamInviteCode> findRecentByTeamId(@Param("teamId") Long teamId, Pageable pageable);

    /**
     * 查找使用次数最多的邀请码
     */
    @Query("SELECT tic FROM TeamInviteCode tic WHERE tic.teamId = :teamId " +
           "ORDER BY tic.usedCount DESC")
    List<TeamInviteCode> findMostUsedByTeamId(@Param("teamId") Long teamId, Pageable pageable);

    /**
     * 批量更新过期状态
     */
    @Query("UPDATE TeamInviteCode tic SET tic.status = 'EXPIRED', tic.updatedAt = :now " +
           "WHERE tic.status = 'ACTIVE' AND tic.expireTime IS NOT NULL AND tic.expireTime <= :now")
    int updateExpiredCodes(@Param("now") LocalDateTime now);

    /**
     * 批量更新用完状态
     */
    @Query("UPDATE TeamInviteCode tic SET tic.status = 'EXHAUSTED', tic.updatedAt = :now " +
           "WHERE tic.status = 'ACTIVE' AND tic.maxUses IS NOT NULL AND tic.usedCount >= tic.maxUses")
    int updateExhaustedCodes(@Param("now") LocalDateTime now);

    /**
     * 查找团队的永久邀请码
     */
    @Query("SELECT tic FROM TeamInviteCode tic WHERE tic.teamId = :teamId " +
           "AND tic.status = 'ACTIVE' AND tic.expireTime IS NULL AND tic.maxUses IS NULL")
    List<TeamInviteCode> findPermanentByTeamId(@Param("teamId") Long teamId);

    /**
     * 查找团队的临时邀请码
     */
    @Query("SELECT tic FROM TeamInviteCode tic WHERE tic.teamId = :teamId " +
           "AND tic.status = 'ACTIVE' AND (tic.expireTime IS NOT NULL OR tic.maxUses IS NOT NULL)")
    List<TeamInviteCode> findTemporaryByTeamId(@Param("teamId") Long teamId);
}
